﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Web;
using System.IO;

namespace Common.SYH
{
    public class FinanceHelper
    {
        public static string GetBusinessName(object type)
        {
            if (type.ToString() == "-1")
                return "未知";
            Common.XmlHelper.SearchXml searchXml = delegate(string value)
            {
                if (value == type.ToString())
                    return true;
                else
                    return false;
            };
            return Common.XmlHelper.Readrepeat(HttpContext.Current.Server.MapPath("~/App_Data/FinanceBusinessType.xml"),
                                                             "/root/item", searchXml, "id", "name");
        }


        public static string GetPaymentType(object type)
        {
            if (type.ToString() == "-1")
                return "未知";
            Common.XmlHelper.SearchXml searchXml = delegate(string value)
            {
                if (value == type.ToString())
                    return true;
                else
                    return false;
            };
            return Common.XmlHelper.Readrepeat(HttpContext.Current.Server.MapPath("~/App_Data/FinancePaymentType.xml"),
                                                             "/root/item", searchXml, "id", "name");
        }

        /// <summary>   
        /// 直接导出Excel   
        /// </summary>   
        /// <param name="table">数据源DataTable</param>   
        /// <returns></returns>   
        //public static string DoExport(System.Data.DataTable table)
        //{
        //    try
        //    {
        //        Application excel = new ApplicationClass();
        //        int rowindex = 1;
        //        int colindex = 0;
        //        Workbook work = excel.Workbooks.Add(true);
        //        //Worksheet sheet1 = (Worksheet)work.Worksheets[0];   
        //        //System.Data.DataTable table = ds.Tables[0];   
        //        foreach (DataColumn col in table.Columns)
        //        {
        //            colindex++;
        //            excel.Cells[1, colindex] = col.ColumnName;
        //        }
        //        foreach (DataRow row in table.Rows)
        //        {
        //            rowindex++;
        //            colindex = 0;
        //            foreach (DataColumn col in table.Columns)
        //            {
        //                colindex++;
        //                excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
        //            }
        //        }
        //        excel.Visible = false;
        //        //((Worksheet)work.Sheets[0]).Name = "sss";  
        //        string fileName = DateTime.Now.ToString("yyyyMMddhhmmss");
        //        string filePath =
        //            HttpContext.Current.Server.MapPath("~/FinanceFile/DoExport/" + fileName +
        //                                               ".xls");
        //        excel.ActiveWorkbook.SaveAs(filePath, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
        //        excel.Quit();
        //        excel = null;
        //        GC.Collect();
        //        return fileName;
        //    }
        //    catch(Exception e)
        //    {
        //        return  "1010"+e.Message;
        //    }
        //}



        /// <summary>
        /// 获取链接字符串
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <returns></returns>
        public static string GetExcelConnection(string strFilePath)
        {
            if (!File.Exists(strFilePath))
            {
                throw new Exception("指定的Excel文件不存在！");
            }
            return
                @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                @"Data Source=" + strFilePath + ";" +
            @"Extended Properties=" + Convert.ToChar(34).ToString() +
            @"Excel 8.0;" + "HDR=Yes;" + Convert.ToChar(34).ToString();
        }

        /// <summary>
        /// 获取与本地DataSet中指定列的类型对应的OleDbType的数据类型字符串
        /// </summary>
        /// <param name="dataColumn"></param>
        /// <returns></returns>
        public static string GetOleDataType(DataColumn dataColumn)
        {
            switch (dataColumn.DataType.Name)
            {
                case "String"://字符串
                    {
                        return "VarChar";
                    }
                case "Double"://数字
                    {
                        return "Double";
                    }
                case "Decimal"://数字
                    {
                        return "Decimal";
                    }
                case "DateTime"://时间
                    {
                        return "Date";
                    }
                default://
                    {
                        return "VarChar";
                    }
            }
        }

        /// <summary>
        /// 获取与本地DataSet中指定列的类型对应的OleDbType类型
        /// </summary>
        /// <param name="dataColumn"></param>
        /// <returns></returns>
        public static OleDbType GetRefOleDataType(DataColumn dataColumn)
        {
            switch (dataColumn.DataType.Name)
            {
                case "String"://字符串
                    {
                        return OleDbType.VarChar;
                    }
                case "Double"://数字
                    {
                        return OleDbType.Double;
                    }
                case "Decimal"://数字
                    {
                        return OleDbType.Decimal;
                    }
                case "DateTime"://时间
                    {
                        return OleDbType.Date;
                    }
                default:
                    {
                        return OleDbType.VarChar;
                    }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt">数据源（数据表）</param>
        /// <param name="ExcelFileName">要导出的Ｅｘｃｌｅ文件，此文件必须已存在</param>
        ///// <param name="ModelFile">ModelFile为模板文件，该文件与数据源中的表一致,否则数据会导出失败。
        ///// ModelFile文件里，需要有一张 与 dt.TableName 一致的表，而且字段也要一致。
        ///// 注明：如果不用ModelFile的话，可以用一个空白Excel文件，
        ///// 不过，要去掉下面创建表的注释，让OleDb自己创建一个空白表。</param>
        /// <returns></returns>
        public static string ExportTable2ExcelFile(DataTable dt, string ExcelFileName)//, string ModelFile)
        {
            if (File.Exists(ExcelFileName) == false)
            {
                //FileStream tt = new FileStream(ExcelFileName, FileMode.Create);
                //tt.Close();
                File.Copy(HttpContext.Current.Server.MapPath("~/FinanceFile/DoExport/mould.xls"), ExcelFileName , true); 
                //return "指定文件不存在！";
            }
            if (dt == null)
            {
                return "数据不能为空！";
            }
            //if (strWorkSheetName.ToString() == "")
            //{
            //    return "数据表名不可以为空！";
            //}
            //dt.TableName = strWorkSheetName;

            int iRows = dt.Rows.Count;
            int iCols = dt.Columns.Count;

            StringBuilder stringBuilder;
            string connString;

            if (iRows == 0)
            {
                return "没有可导入数据！";
            }

            stringBuilder = new StringBuilder();


            connString = GetExcelConnection(ExcelFileName);// "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFileName + ";Extended Properties=Excel 8.0;";

            //先查看此Excel中是否有相关Table，如果有的话就删除，然后导入新的。
            //生成创建表的脚本
            stringBuilder.Append("CREATE TABLE ");
            stringBuilder.Append("["+dt.TableName + "] ( ");
            for (int i = 0; i < iCols; i++)
            {
                //此处是本版本改进中最实用的地方
                string strType = GetOleDataType(dt.Columns[i]);
                if (i < iCols - 1)
                    stringBuilder.Append(string.Format("[{0}] {1},", dt.Columns[i].ColumnName, strType));
                else
                    stringBuilder.Append(string.Format("[{0}] {1})", dt.Columns[i].ColumnName, strType));
            }

            using (OleDbConnection objConn = new OleDbConnection(connString))
            {
                //控制进度条
                int iCount = 1, iTotal = dt.Rows.Count + 4;
                //progressBar.Minimum = Convert.ToInt32(100 / iTotal);
                //progressBar.Maximum = 100;
                //progressBar.Step = 1;

                OleDbCommand objCmd = new OleDbCommand();
                objCmd.Connection = objConn;

                //插入新表
                objCmd.CommandText = stringBuilder.ToString();

                try
                {
                    objConn.Open();
                    //插入新表
                    objCmd.ExecuteNonQuery();
                    //progressBar.PerformStep();
                }
                catch (Exception e)
                {
                    return "在Excel中创建表失败！错误信息：" + e.Message;
                }

                stringBuilder.Remove(0, stringBuilder.Length);

                stringBuilder.Append("INSERT INTO ");
                stringBuilder.Append(dt.TableName + " ( ");

                //先插入标头
                for (int i = 0; i < iCols; i++)
                {
                    if (i < iCols - 1)
                        stringBuilder.Append(dt.Columns[i].ColumnName + ",");
                    else
                        stringBuilder.Append(dt.Columns[i].ColumnName + ") values (");
                }

                for (int i = 0; i < iCols; i++)
                {

                    if (i < iCols - 1)
                        stringBuilder.Append("@" + dt.Columns[i].ColumnName + ",");
                    else
                        stringBuilder.Append("@" + dt.Columns[i].ColumnName + ")");
                }

                //progressBar.PerformStep();

                //建立插入动作的Command
                objCmd.CommandText = stringBuilder.ToString();
                OleDbParameterCollection oleParam = objCmd.Parameters;

                oleParam.Clear();
                for (int i = 0; i < iCols; i++)
                {
                    OleDbType oleDbType = GetRefOleDataType(dt.Columns[i]);

                    //此处是本版本改进中最实用的地方
                    oleParam.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, oleDbType));
                }
                //progressBar.PerformStep();

                //遍历DataTable将数据插入新建的Excel文件中
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < oleParam.Count; i++)
                    {
                        oleParam[i].Value = row[i];
                    }

                    objCmd.ExecuteNonQuery();

                    //progressBar.PerformStep();

                }
                //stringBuilder.Remove(0, stringBuilder.Length);
                //stringBuilder.Append("drop table Sheet1");
                //objCmd.CommandText = stringBuilder.ToString();
                //objCmd.ExecuteNonQuery();
                //progressBar.Value = progressBar.Maximum;
                return "数据已成功导入Excel！";
            }
        }
    }
}
